﻿using STHPMS1.DAL.D_Core;
using STHPMS1.ICore.I_DAL.Repository;
using STHPMS1.ICore.I_Entity.Stmpms1;
using System.Collections.Generic;
using System.Data;
using ServiceStack.OrmLite;
using System.Text;
using System.Data.SqlClient;
using System;
using System.Linq.Expressions;
using ServiceStack.OrmLite.SqlServer;

namespace STHPMS1.DAL.D_Repository
{
    public class Dn_Divi_ResultRepository : DalBase, IDn_Divi_ResultRepository
    {
        public IEnumerable<T> GetMainNeedWay<T>(string rdwaryId) where T : class, IDn_Divi_Result
        {
            string sql = string.Format(@"select distinct divi_time,MAINNEED_WAY from DN_DIVI_RESULT where RDWAYID='{0}' and left(divi_time,4)=DATENAME(yy,getdate())", rdwaryId);
            IDbConnection conn = Adapter.OpenDbConnection();
            return conn.Select<T>(sql);
        }

        public IEnumerable<T> GetResulst<T>(IB_Roadnet roadnet)where T : class, IDn_Divi_Result
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from  DN_DIVI_RESULT where 1=1");
            if(roadnet.Admdiv!= null && roadnet.Admdiv!="")
            {
                //sql.AppendFormat(" and admdiv='{0}'", roadnet.Admdiv); //行政区域
                  if (roadnet.Admdiv.Substring(2) == "0000") //省级
                {
                    //roadnet.Admdiv.Substring(0,2)
                    sql.Append( " and ADMDIV like '%" + roadnet.Admdiv.Substring(0, 2) + "%' ");
                }
                else if (roadnet.Admdiv.Substring(4) == "00") //市级
                {
                    //roadnet.Admdiv.Substring(0,4)
                    sql.Append( " and ADMDIV like '%" + roadnet.Admdiv.Substring(0, 4) + "%'" );
                }
                else  //区域
                {
                    sql.Append( " and ADMDIV='"+roadnet.Admdiv+"'");
                }
            }
            if (roadnet.Admgrd != null && roadnet.Admgrd != "") //行政等级
            {
                sql.AppendFormat(" and Admgrd in({0})", roadnet.Admgrd);
            }
            if (roadnet.Rdclass_Dictid != null && roadnet.Rdclass_Dictid != "") //技术等级
            {
                sql.AppendFormat(" and Rdclass_Dictid in ({0})", roadnet.Rdclass_Dictid);
            }
            if (roadnet.Pvmttype != null && roadnet.Pvmttype != "") //面层类型
            {
                sql.AppendFormat(" and Pvmttype_dictid ='{0}'", roadnet.Pvmttype);
            }
            if (roadnet.Manaut != null && roadnet.Manaut != "") //管养单位
            {
                sql.AppendFormat(" and Manaut ='{0}'", roadnet.Manaut);
            }
            IDbConnection conn = Adapter.OpenDbConnection();
            return conn.Select<T>(sql.ToString());
        }


        public void Update(IDn_Divi_Result[] diviList)
        {
            StringBuilder sql = new StringBuilder();
            foreach(IDn_Divi_Result divi in diviList)
            {
                sql.AppendFormat("update DN_DIVI_RESULT set MAINNEED_WAY='{0}',SEGID_MAIN={1} where MAINSEGID='{2}';", 
                    divi.Mainneed_Way, divi.Segid_Main, divi.Mainsegid);
                if(divi.Pci!=null)
                {
                    sql.AppendFormat("update DN_DIVI_RESULT set PCI={0} where MAINSEGID='{1}';", divi.Pci, divi.Mainsegid);
                }
                if (divi.Pqi != null)
                {
                    sql.AppendFormat("update DN_DIVI_RESULT set PQI={0} where MAINSEGID='{1}';", divi.Pqi, divi.Mainsegid);
                }
                if (divi.Pssi != null)
                {
                    sql.AppendFormat("update DN_DIVI_RESULT set PSSI={0} where MAINSEGID='{1}';", divi.Pssi, divi.Mainsegid);
                }
                if (divi.Rqi != null)
                {
                    sql.AppendFormat("update DN_DIVI_RESULT set RQI={0} where MAINSEGID='{1}';", divi.Rqi, divi.Mainsegid);
                }
                if (divi.Sri != null)
                {
                    sql.AppendFormat("update DN_DIVI_RESULT set SRI={0} where MAINSEGID='{1}';", divi.Sri, divi.Mainsegid);
                }
                if (divi.Rl != null)
                {
                    sql.AppendFormat("update DN_DIVI_RESULT set RL={0} where MAINSEGID='{1}';", divi.Rl, divi.Mainsegid);
                }
            }
            using (IDbConnection conn = Adapter.OpenDbConnection())
            {
                IDbTransaction tran = conn.BeginTransaction();
                try
                {
                    IDbCommand cmmd = conn.CreateCommand();
                    cmmd.Transaction = tran;
                    cmmd.CommandText = sql.ToString();
                    cmmd.ExecuteNonQuery();
                    tran.Commit();
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    throw ex;
                }
            }
        }
        public void Clear(string rdid,string gf_zb)
        {
            StringBuilder sql = new StringBuilder();
            //删除制定划分类型的养护路段
            string sqlDelDivi = string.Format("delete from DN_DIVI_RESULT where RDWAYID='{0}' and MAINNEED_WAY='{1}'",rdid,gf_zb);
            //删除养护路段和单元的关联
            string sqlDelUnit = string.Format("delete from B_INVNTORY_UNIT where SEGID in(select MAINSEGID from DN_DIVI_RESULT where RDWAYID='{0}' and MAINNEED_WAY='{1}')", rdid, gf_zb);
            sql.Append(sqlDelUnit); //先删除关联表
            sql.Append(";");
            sql.Append(sqlDelDivi);//再删除养护路段
            using (IDbConnection conn = Adapter.OpenDbConnection())
            {
                IDbTransaction tran = conn.BeginTransaction();
                try
                {
                    IDbCommand cmmd = conn.CreateCommand();
                    cmmd.Transaction = tran;
                    cmmd.CommandText = sql.ToString(); 
                    cmmd.ExecuteNonQuery();
                    tran.Commit();
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    throw ex;
                }
            }
        }

        public void ClearOtherChart(string rdid,string dir)
        {
            StringBuilder sql = new StringBuilder();
            //删除制定划分类型的养护路段
            string sqlDelDivi = string.Format("delete from DN_DIVI_RESULT where RDWAYID='{0}' and DIRECTION_DICTID={1}", rdid, dir);
            //删除养护路段和单元的关联
            string sqlDelUnit = string.Format("delete from B_INVNTORY_UNIT where SEGID in(select MAINSEGID from DN_DIVI_RESULT where RDWAYID='{0}' and DIRECTION_DICTID={1})", rdid, dir);
            sql.Append(sqlDelUnit); //先删除关联表
            sql.Append(";");
            sql.Append(sqlDelDivi);//再删除养护路段
            using (IDbConnection conn = Adapter.OpenDbConnection())
            {
                IDbTransaction tran = conn.BeginTransaction();
                try
                {
                    IDbCommand cmmd = conn.CreateCommand();
                    cmmd.Transaction = tran;
                    cmmd.CommandText = sql.ToString();
                    cmmd.ExecuteNonQuery();
                    tran.Commit();
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    throw ex;
                }
            }
        }

        public void ClearOther(string rdid, string gf_zb)
        {
            StringBuilder sql = new StringBuilder();
            //删除制定划分类型的养护路段
            string sqlDelDivi = string.Format("delete from DN_DIVI_RESULT where RDWAYID='{0}' and MAINNEED_WAY!='{1}'", rdid, gf_zb);
            //删除养护路段和单元的关联
            string sqlDelUnit = string.Format("delete from B_INVNTORY_UNIT where SEGID in(select MAINSEGID from DN_DIVI_RESULT where RDWAYID='{0}' and MAINNEED_WAY!='{1}')", rdid, gf_zb);
            sql.Append(sqlDelUnit); //先删除关联表
            sql.Append(";");
            sql.Append(sqlDelDivi);//再删除养护路段
            using (IDbConnection conn = Adapter.OpenDbConnection())
            {
                IDbTransaction tran = conn.BeginTransaction();
                try
                {
                    IDbCommand cmmd = conn.CreateCommand();
                    cmmd.Transaction = tran;
                    cmmd.CommandText = sql.ToString();
                    cmmd.ExecuteNonQuery();
                    tran.Commit();
                }
                catch (Exception ex)
                {
                    tran.Rollback();
                    throw ex;
                }
            }
        }

        public IEnumerable<T> GetNewSegment<T>(string rdwaryId, string m_w, ICore.I_Entity.Info.IPagingInfo info)
             where T : class, IDn_Divi_Result
        {
            string sql = string.Format(@"select count(*) r from DN_DIVI_RESULT where divi_time=(select max(divi_time) 
                        from DN_DIVI_RESULT where RDWAYID= '{0}'and MAINNEED_WAY='{1}')
                            ", rdwaryId, m_w);
            IDbConnection conn = Adapter.OpenDbConnection();
            info.TotalRecords = Convert.ToInt32(conn.Column<string>(sql)[0]);



            sql = string.Format(@"select top {2} * from (select * from DN_DIVI_RESULT where divi_time=(select max(divi_time) 
from DN_DIVI_RESULT where RDWAYID= '{0}'and MAINNEED_WAY='{1}')) as t 
where t.mainsegid not in(select top ({2}*({3}-0)) m.mainsegid from (select * from DN_DIVI_RESULT where divi_time=(select max(divi_time) 
from DN_DIVI_RESULT where RDWAYID= '{0}'and MAINNEED_WAY='{1}') ) as m order by m.BEGINMP)order by t.BEGINMP", rdwaryId, m_w, info.PageSize, info.PageIndex);
            return conn.Select<T>(sql);
        }

        public IEnumerable<T> GetMainSegmentUnit<T>(string rdwaryId)
            where T : class, IB_Invntory_Unit
        {
            string sql = string.Format(@"select DISTINCT u.* from B_INVNTORY_UNIT as u 
RIGHT JOIN (select * from DN_DIVI_RESULT where divi_time=(select max(divi_time) from DN_DIVI_RESULT where RDWAYID= '{0}') and RDWAYID='{0}') as d
on u.SEGID=d.SEGID
", rdwaryId);
            IDbConnection conn = Adapter.OpenDbConnection();
            return conn.Select<T>(sql);
        }


        public int GetLastYear(string rdwayid, string mainsegid)
        {
            IDbConnection conn = Adapter.OpenDbConnection();
            return conn.Scalar<int>("select LASTMAIN_YEAR from DN_DIVI_RESULT where RDWAYID='" + rdwayid + "' and MAINSEGID='" + mainsegid + "'");
        }

        public DataRow GetTe_Data(IDn_Divi_Result r)
        {
             DataTable dt = new DataTable();
             using (SqlConnection conn = new SqlConnection(Adapter.ConnectionString))
             {
                 string sql = string.Format(@"select AVG(a.pci) pci ,AVG(a.pssi) pssi,AVG(a.rdi) rdi,AVG(a.rqi) rqi,AVG(a.sri) sri
 from E_ROADWAYDETAIL a where not exists(select 1 
from E_ROADWAYDETAIL b where b.unit=a.unit and b.YEAR_M>a.YEAR_M)
and a.RDWAYID='{0}' and DIRECTION_DICTID={1} 
and a.UNIT in (select unit from B_INVNTORY_UNIT where SEGID='{2}')", r.Rdwayid, r.Direction_Dictid, r.Mainsegid);
                 using (SqlDataAdapter da = new SqlDataAdapter(sql, conn))
                 {
                     da.Fill(dt);
                     da.FillSchema(dt, SchemaType.Source);
                     if (dt.Rows.Count > 0)
                         return dt.Rows[0];
                     return null;
                 }
             }
        }

        public double GetRoadLenght(string rdwayid, string mainsegid)
        {
            IDbConnection conn = Adapter.OpenDbConnection();
            return conn.Scalar<double>("select ENDMP-BEGINMP from DN_DIVI_RESULT where RDWAYID='" + rdwayid + "' and MAINSEGID='" + mainsegid + "'");
        }
        public double GetRoadAera(string rdwayid, string mainsegid)
        {
            IDbConnection conn = Adapter.OpenDbConnection();
            return conn.Scalar<double>("select (ENDMP-BEGINMP)*SURFWIDTH from DN_DIVI_RESULT where RDWAYID='" + rdwayid + "' and MAINSEGID='" + mainsegid + "'");
        }

        public IEnumerable<T> GetNewRoad<T>(string rdwaryId, string dir, ICore.I_Entity.Info.IPagingInfo info)
           where T : class, IDn_Divi_Result
        {
            string sql = string.Format(@"select count(*) r from DN_DIVI_RESULT where divi_time=(select max(divi_time) 
                        from DN_DIVI_RESULT where RDWAYID= '{0}'and DIRECTION_DICTID='{1}')
                            ", rdwaryId, dir);
            IDbConnection conn = Adapter.OpenDbConnection();
            info.TotalRecords = Convert.ToInt32(conn.Column<string>(sql)[0]);



            sql = string.Format(@"select top {2} * from (select * from DN_DIVI_RESULT where divi_time=(select max(divi_time) 
from DN_DIVI_RESULT where RDWAYID= '{0}'and DIRECTION_DICTID='{1}')) as t 
where t.mainsegid not in(select top ({2}*({3}-0)) m.mainsegid from (select * from DN_DIVI_RESULT where divi_time=(select max(divi_time) 
from DN_DIVI_RESULT where RDWAYID= '{0}'and DIRECTION_DICTID='{1}') ) as m order by m.BEGINMP)order by t.BEGINMP", rdwaryId, dir, info.PageSize, info.PageIndex);
            return conn.Select<T>(sql);
        }


        /// <summary>
        /// 查询面层厚度
        /// </summary>
        /// <param name="rdid"></param>
        /// <param name="dir"></param>
        /// <param name="start"></param>
        /// <param name="end"></param>
        /// <returns></returns>
        public double GetThick_S(string rdid,int dir,double start,double end)
        {
            using (SqlConnection conn = new SqlConnection(Adapter.ConnectionString))
            {
                conn.Open();
                string sql = string.Format(@"select THICK_S from B_PMSTC where RDWAYID='{0}' and DIRECTION_DICTID={1} and (BEGINMP<={2} and ENDMP>={3})",rdid,dir,start,end);
                SqlCommand cmmd = new SqlCommand(sql, conn);
                object obj = cmmd.ExecLongScalar();
                conn.Close();
                return Convert.ToDouble(obj);
            }
        }

        /// <summary>
        /// 查询中修的上面层和中面层厚度
        /// </summary>
        /// <param name="id">资金模型ID</param>
        /// <returns></returns>
        public DataTable GetThick_Up_Midlle(string id)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(Adapter.ConnectionString))
            {
                string sql = string.Format(@"select * from DR_REPAIR_MID where id='{0}' ", id);
                using (SqlDataAdapter da = new SqlDataAdapter(sql, conn))
                {
                    da.Fill(dt);
                    return dt;
                }
            }
        }
        /// <summary>
        /// 查询大修的上面层和中面层,下面层厚度
        /// </summary>
        /// <param name="id">资金模型ID</param>
        /// <returns></returns>
        public DataTable GetThick_Up_Midlle_Down(string id)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(Adapter.ConnectionString))
            {
                string sql = string.Format(@"select * from DR_REPAIR_LAR where id='{0}' ", id);
                using (SqlDataAdapter da = new SqlDataAdapter(sql, conn))
                {
                    da.Fill(dt);
                    return dt;
                }
            }
        }


        /// <summary>
        /// 获取预防性养护初选措施
        /// </summary>
        /// <param name="anniname"></param>
        /// <param name="year"></param>
        /// <returns></returns>
        public string GetMeas_ini(string anniname,int year)
        {
            using (SqlConnection conn = new SqlConnection(Adapter.ConnectionString))
            {
                conn.Open();
                string sql = string.Format(@"select MEAS_INI from DP_PREVEN_DETAIL
where ANNI_NAME='{0}' and ANALY_YEAR='{1}' and PREVEN_MEASURE=1 and SECON_MEASURE=1", anniname, year);
                SqlCommand cmmd = new SqlCommand(sql, conn);
                object obj = cmmd.ExecLongScalar();
                conn.Close();
                return obj.ToString();
            }
        }


        public DataTable GetRoadAvgZB(string rdid,string dir)
        {
            //and LEFT(a.YEAR_M,4)=DATENAME(YYYY,GETDATE())
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(Adapter.ConnectionString))
            {
                string sql = string.Format(@"select y.SEGID,r.RDCLASS_DICTID,r.BEGINMP,r.ENDMP,avg(x.MQI)MQI,avg(x.PCI)PCI,avg(x.PQI)PQI,avg(x.SRI)SRI,avg(x.PSSI)PSSI,avg(x.RQI) RQI from
(select b.UNIT,e.PCI,e.PQI,e.SRI,e.PSSI,e.RQI,e.MQI 
from B_UNIT b inner join E_ROADWAYDETAIL e on b.UNIT = e.UNIT
where b.UNIT in(select UNIT from B_UNIT where UNIT in(select UNIT from B_INVNTORY_UNIT where SEGID 
in(select SEGID from B_INVNTORY where RDWAYID='{0}' and DIRECTION_DICTID={1})))
and e.INSPECID=(Select top 1 a.INSPECID from E_ROADWAYDETAIL a where a.RDWAYID='{0}' and a.DIRECTION_DICTID={1}

order by a.YEAR_M desc))as x 
inner join B_INVNTORY_UNIT as y on x.unit = y.unit 
inner join DN_DIVI_RESULT as r on y.SEGID = r.MAINSEGID
 group by y.SEGID,r.BEGINMP,r.ENDMP,r.RDCLASS_DICTID
 order by r.BEGINMP", rdid,dir) ; 
                using (SqlDataAdapter da = new SqlDataAdapter(sql, conn))
                {
                    da.Fill(dt);
                    da.FillSchema(dt, SchemaType.Source);
                    return dt;
                }
            }
        }

        #region 模型处理
        /// <summary>
        /// 查询路段累计当量轴载
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="segid"></param>
        /// <returns></returns>
        public IEnumerable<T> GetEsalage<T>(string segid) where T : class, IDm_Esalage
        {
            string sql = string.Format(@"select * from DM_ESALAGE where SEGID='{0}'", segid);
            IDbConnection conn = Adapter.OpenDbConnection();
            return conn.Select<T>(sql);
        }

        /// <summary>
        /// 根据模型组查询模型
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="groupId"></param>
        /// <returns></returns>
        public T GetModelGroup<T>(string groupId) where T:class,IDm_Modelgroup
        {
            string sql = string.Format(@"select * from Dm_Modelgroup where MODELGROUP_ID='{0}'", groupId);
            IDbConnection conn = Adapter.OpenDbConnection();
            return conn.Single<T>(sql);
        }

        #endregion

    }
}
